package dao.mysql;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import modelInterfaces.AbstractModelFactory;
import modelInterfaces.IEvent;
import modelInterfaces.IEventType;
import modelInterfaces.ISubject;
import modelInterfaces.IValue;
import dao.DAOException;
import dao.DAOFactory;
import dao.IEventDAO;

/**
 *
 * @author salvador
 */
public class EventMySqlDAO implements IEventDAO {

	private String PROPERTIES_FILE_PATH = "config/MySqlDAO.props";
	
    private String TABLE_NAME = "event";
	private String ID_COLUMN_NAME = "id";
    private String SUBJECT_COLUMN_NAME = "subject";
    private String OBJECT_COLUMN_NAME = "object";
    private String TIME_COLUMN_NAME = "time";
    private String EVENT_TYPE_COLUMN_NAME = "eventType";
    
    private String VALUE_TABLE_NAME = "value";
    private String VALUE_ID_COLUMN_NAME = "id";
    private String VALUE_VALUE_COLUMN_NAME = "value";
    private String VALUE_EVENT_ID_COLUMN_NAME = "event";
	
    public EventMySqlDAO(){
    	Properties prop = new Properties();
		try {
			prop.load(new FileInputStream(PROPERTIES_FILE_PATH));
			TABLE_NAME = 				prop.getProperty("EVENT_TABLE_NAME") != null ? 				prop.getProperty("EVENT_TABLE_NAME") : 				this.TABLE_NAME;
	        ID_COLUMN_NAME = 			prop.getProperty("EVENT_ID_COLUMN_NAME") != null ? 			prop.getProperty("EVENT_ID_COLUMN_NAME") : 			this.ID_COLUMN_NAME;
	        SUBJECT_COLUMN_NAME = 		prop.getProperty("EVENT_SUBJECT_COLUMN_NAME") != null ? 	prop.getProperty("EVENT_SUBJECT_COLUMN_NAME") : 	this.SUBJECT_COLUMN_NAME;
	        OBJECT_COLUMN_NAME = 		prop.getProperty("EVENT_OBJECT_COLUMN_NAME") != null ?		prop.getProperty("EVENT_OBJECT_COLUMN_NAME") : 		this.OBJECT_COLUMN_NAME;
	        TIME_COLUMN_NAME = 			prop.getProperty("EVENT_TIME_COLUMN_NAME") != null ? 		prop.getProperty("EVENT_TIME_COLUMN_NAME") : 		this.TIME_COLUMN_NAME;
	        EVENT_TYPE_COLUMN_NAME = 	prop.getProperty("EVENT_EVENT_TYPE_COLUMN_NAME") != null ? 	prop.getProperty("EVENT_EVENT_TYPE_COLUMN_NAME") : 	this.EVENT_TYPE_COLUMN_NAME;
	        
	        VALUE_TABLE_NAME = 				prop.getProperty("VALUE_TABLE_NAME") 			!= null ? prop.getProperty("VALUE_TABLE_NAME") : 			this.VALUE_TABLE_NAME;
	        VALUE_ID_COLUMN_NAME = 			prop.getProperty("VALUE_ID_COLUMN_NAME") 		!= null ? prop.getProperty("VALUE_ID_COLUMN_NAME") : 		this.VALUE_ID_COLUMN_NAME ;
	        VALUE_EVENT_ID_COLUMN_NAME = 	prop.getProperty("VALUE_EVENT_ID_COLUMN_NAME") 	!= null ? prop.getProperty("VALUE_EVENT_ID_COLUMN_NAME") : 	this.VALUE_EVENT_ID_COLUMN_NAME ;
	        VALUE_VALUE_COLUMN_NAME = 		prop.getProperty("VALUE_VALUE_COLUMN_NAME") 	!= null ? prop.getProperty("VALUE_VALUE_COLUMN_NAME") : 	this.VALUE_VALUE_COLUMN_NAME ;
	        
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
    }
    
	
	public void create(IEvent event) throws DAOException
    {
        MySqlDBConnection.getInstance().executeUpdate("INSERT INTO "+ TABLE_NAME +"("+ ID_COLUMN_NAME +", "+ SUBJECT_COLUMN_NAME +", "+ OBJECT_COLUMN_NAME +", "+ TIME_COLUMN_NAME +", "+ EVENT_TYPE_COLUMN_NAME +") VALUES("
                +event.getId()+","
                +"'"+event.getSubject()+"',"
                +"'"+event.getObject()+"',"
                +"'"+event.getTime()+"',"
                +event.getEventType().getId()
                +")");

        Iterator<IValue> iterator = event.getValues().iterator();
        while(iterator.hasNext())
        {
            MySqlDBConnection.getInstance().executeUpdate("INSERT INTO "+ VALUE_TABLE_NAME +"("+ VALUE_VALUE_COLUMN_NAME +", "+ VALUE_EVENT_ID_COLUMN_NAME +") VALUES("
                    +"'"+iterator.next().getValue()+"',"
                    +event.getId()
                    +")");
        }
    }

    public List<IEvent> retrieveAll() throws DAOException {
    	ResultSet rs = MySqlDBConnection.getInstance().executeQuery("SELECT * FROM "+ TABLE_NAME);
        List<IEvent> eventList = new ArrayList<IEvent>();
        try
        {
            while (rs.next())
            {
                IEvent event = AbstractModelFactory.getModelFactory().getEvent();
                event.setId(rs.getInt(ID_COLUMN_NAME));
                event.setSubject(rs.getString(SUBJECT_COLUMN_NAME));
                event.setObject(rs.getString(OBJECT_COLUMN_NAME));
                event.setTime(rs.getString(TIME_COLUMN_NAME));
                event.setEventType(DAOFactory.getDAOFactory().getEventTypeDAO().retrieve(Integer.parseInt(rs.getString(EVENT_TYPE_COLUMN_NAME))));
                event.setValues(DAOFactory.getDAOFactory().getValueDAO().retrieve(event));
                eventList.add(event);
            }
        }
        catch (SQLException ex)
        {
            throw new DAOException(ex.getErrorCode(), ex.getMessage());
        }
        return eventList;
    }


	
    @Override
	public List<IEvent> retrieveAll(ISubject subject) throws DAOException {
    	String query = "SELECT * FROM "+ TABLE_NAME +" where "+ SUBJECT_COLUMN_NAME +" ='"+subject.getId()+"'"; 
        ResultSet rs = MySqlDBConnection.getInstance().executeQuery(query);
        List<IEvent> eventList = new ArrayList<IEvent>();
        try
        {
            while (rs.next())
            {
                IEvent event = AbstractModelFactory.getModelFactory().getEvent();
                event.setId(rs.getInt(ID_COLUMN_NAME));
                event.setSubject(rs.getString(SUBJECT_COLUMN_NAME));
                event.setObject(rs.getString(OBJECT_COLUMN_NAME));
                event.setTime(rs.getString(TIME_COLUMN_NAME));
                event.setEventType(DAOFactory.getDAOFactory().getEventTypeDAO().retrieve(Integer.parseInt(rs.getString(EVENT_TYPE_COLUMN_NAME))));
                event.setValues(DAOFactory.getDAOFactory().getValueDAO().retrieve(event));
                eventList.add(event);
            }
        }
        catch (SQLException ex)
        {
            throw new DAOException(ex.getErrorCode(), ex.getMessage());
        }
        
        
        return eventList;
	}
    
    public List<IEvent> retrieveAllFromType(ISubject subject, int type) throws DAOException {

        String query = "SELECT * FROM "+ TABLE_NAME +" where "+ SUBJECT_COLUMN_NAME +" ='"+subject.getId()+"'"+ " AND "+ EVENT_TYPE_COLUMN_NAME +"='"+ type +"'";  
    	
        ResultSet rs = MySqlDBConnection.getInstance().executeQuery(query);
        List<IEvent> eventList = new ArrayList<IEvent>();
        try
        {
            while (rs.next())
            {
                IEvent event = AbstractModelFactory.getModelFactory().getEvent();
                event.setId(rs.getInt(ID_COLUMN_NAME));
                event.setSubject(rs.getString(SUBJECT_COLUMN_NAME));
                event.setObject(rs.getString(OBJECT_COLUMN_NAME));
                event.setTime(rs.getString(TIME_COLUMN_NAME));
                event.setEventType(DAOFactory.getDAOFactory().getEventTypeDAO().retrieve(Integer.parseInt(rs.getString(EVENT_TYPE_COLUMN_NAME))));
                event.setValues(DAOFactory.getDAOFactory().getValueDAO().retrieve(event));
                eventList.add(event);
            }
        }
        catch (SQLException ex)
        {
            throw new DAOException(ex.getErrorCode(), ex.getMessage());
        }
        return eventList;
	}
    
    public List<IEvent> retrieveAllFromType(int type) throws DAOException {
    	String query = "SELECT * FROM "+ TABLE_NAME +" where "+ EVENT_TYPE_COLUMN_NAME +" ='"+ type +"'";
    	ResultSet rs = MySqlDBConnection.getInstance().executeQuery(query);
        List<IEvent> eventList = new ArrayList<IEvent>();
        try
        {
            while (rs.next())
            {
                IEvent event = AbstractModelFactory.getModelFactory().getEvent();
                event.setId(rs.getInt(ID_COLUMN_NAME));
                event.setSubject(rs.getString(SUBJECT_COLUMN_NAME));
                event.setObject(rs.getString(OBJECT_COLUMN_NAME));
                event.setTime(rs.getString(TIME_COLUMN_NAME));
                event.setEventType(DAOFactory.getDAOFactory().getEventTypeDAO().retrieve(Integer.parseInt(rs.getString(EVENT_TYPE_COLUMN_NAME))));
                event.setValues(DAOFactory.getDAOFactory().getValueDAO().retrieve(event));
                eventList.add(event);
            }
        }
        catch (SQLException ex)
        {
            throw new DAOException(ex.getErrorCode(), ex.getMessage());
        }
        return eventList;       
    }

	@Override
	public int totalAmount() throws DAOException {
		String query = "SELECT COUNT(*) FROM "+ TABLE_NAME;
		ResultSet rs = MySqlDBConnection.getInstance().executeQuery(query);
		
		int rowCount;
		try
		{
			rs.next();
			rowCount = rs.getInt(1);
		}
		catch (SQLException ex)
		{
			throw new DAOException(ex.getErrorCode(), ex.getMessage());
		}
		
		return rowCount;
	}

	@Override
	public List<IEvent> retrieveAllFromTypes(List<IEventType> eventTypes)
			throws DAOException {
    	
		return getEvents(getEventsQuery(eventTypes), false, false);
		 
	}
	
	public List<IEvent> retrieveAllFromTypes(List<IEventType> eventTypes, boolean hasEventTypes, boolean hasValues) throws DAOException {
    	
		return getEvents(getEventsQuery(eventTypes), hasEventTypes, hasValues);
	}	
	
	private String getEventsQuery(List<IEventType> eventTypes)
	{
		String query = "SELECT * FROM "+ TABLE_NAME +" WHERE ";
		Iterator<IEventType> iterator = eventTypes.iterator();
		
        boolean isFirstLoop = true;
		while(iterator.hasNext())
        {
        	if (!isFirstLoop)
        	{
        		query += " OR ";
        	}
        	query += EVENT_TYPE_COLUMN_NAME +"= '"+ iterator.next().getId() +"'";
        	isFirstLoop = false;
        }
		
		return query;
	}
	
	private List<IEvent> getEvents(String query, boolean hasEventType, boolean hasValues) throws DAOException
	{
		ResultSet rs = MySqlDBConnection.getInstance().executeQuery(query);
        List<IEvent> eventList = new ArrayList<IEvent>();
        try
        {
            while (rs.next())
            {
                IEvent event = AbstractModelFactory.getModelFactory().getEvent();
                event.setId(rs.getInt(ID_COLUMN_NAME));
                event.setSubject(rs.getString(SUBJECT_COLUMN_NAME));
                event.setObject(rs.getString(OBJECT_COLUMN_NAME));
                event.setTime(rs.getString(TIME_COLUMN_NAME));
                if (hasEventType)
                {
                	event.setEventType(DAOFactory.getDAOFactory().getEventTypeDAO().retrieve(Integer.parseInt(rs.getString(EVENT_TYPE_COLUMN_NAME))));
                }
                if (hasValues)
                {
                	event.setValues(DAOFactory.getDAOFactory().getValueDAO().retrieve(event));
                }
                eventList.add(event);
            }
        }
        catch (SQLException ex)
        {
            throw new DAOException(ex.getErrorCode(), ex.getMessage());
        }
        return eventList;		
	}
	
    public void delete(IEvent event) throws DAOException {
    	// TODO [programador] Auto-generated method stub
    }

    public void update(IEvent event) throws DAOException {
    	// TODO [programador] Auto-generated method stub
    }

    public IEvent retrieve() throws DAOException {
    	// TODO [programador] Auto-generated method stub
    	return null;
    }

}
